﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace _201206QuanLyBanHang.BaoCao
{
    class BLL_BaoCao
    {
        DataConn da = new DataConn();
        //Danh sách báo công nợ theo khách hàng
        public DataTable getDSCongNokh(string tencuahang)
        {
            DataTable dt = new DataTable();
            DataSet ds = new DataSet();
            //Makh=0 select all
            if (tencuahang == "0")
            {
                string str = "select row_number() over (order by t1.makh) as STT, t2.tencuahang,  t1.namtongcongno, tongcongno " +
                            "from tongkhcongno t1, khachhang t2 " +
                            "where t1.makh=t2.makh and tongcongno <> 0 and datepart(M,t1.namtongcongno)=datepart(M,'"+DateTime.Now + "')" +
                            "order by t2.makhuvuc";
                dt = da.ExectueQueryBaoCao1(str, CommandType.Text);
                //DateTime.Today.Day();
            }
            else
            {
                string str = "select row_number() over (order by t1.makh) as STT, t2.tencuahang,  t1.namtongcongno, tongcongno " +
                            "from tongkhcongno t1, khachhang t2 " +
                            "where t1.makh=t2.makh and tongcongno <> 0  and datepart(M,t1.namtongcongno)=datepart(M,'"+DateTime.Now + "')" +
                            "and t2.tencuahang= N'" + tencuahang +"' "+
                            " order by t2.makhuvuc";
                dt = da.ExectueQueryBaoCao1(str, CommandType.Text);
            }
            return dt;
        }

        //Danh sách báo công nợ theo khu vực
        public DataTable getDSCongNoKhuVuc(string tenkhuvuc)
        {
            DataTable dt = new DataTable();
            DataSet ds = new DataSet();
            //Makh=0 select all
            if (tenkhuvuc == "0")
            {
                string str = "select row_number() over (order by t1.makh) as STT, t2.tencuahang,  t1.namtongcongno, tongcongno " +
                            "from tongkhcongno t1, khachhang t2 " +
                            "where t1.makh=t2.makh and tongcongno <> 0  and datepart(M,t1.namtongcongno)=datepart(M,'" + DateTime.Now + "')" +
                            "order by t2.makhuvuc";
                dt = da.ExectueQueryBaoCao1(str, CommandType.Text);
            }
            else
            {
                string str = "select row_number() over (order by t1.makh) as STT, t2.tencuahang,  t1.namtongcongno, tongcongno " +
                            " from tongkhcongno t1, khachhang t2, khuvuc t3 " +
                            " where t1.makh=t2.makh and tongcongno <> 0 and datepart(M,t1.namtongcongno)=datepart(M,'" + DateTime.Now + "')" +
                            " and t2.makhuvuc=t3.makhuvuc and t3.tenkhuvuc=N'" + tenkhuvuc + "' " +
                            " order by t2.makhuvuc";
                dt = da.ExectueQueryBaoCao1(str, CommandType.Text);
            }
            return dt;
        }

        // Doanh thu Khu vực A năm 2012 trong 1 tháng 
        public DataTable getDoanhThuKhuVuc(string tenkhuvuc)
        {
            DataTable dt = new DataTable();
            //Makh=0 select all
            if (tenkhuvuc == "0")
            {
                string str = "select t1.stt, t1.tencuahang, t2.masp, sum(soluongmua)as soluong, "
                            + "(t2.gia_sp_ngaymua +t2.gia_chietkhau_ngaymua) as dongia,gia_sp_ngaymua as giagoc, sum(t2.thanhtien) as thanhtien, "
                            +"t3.tongthanhtoan as thanhtoan, t4.tongcongno as congno, t2.ghichu_khmsp as ghichu "
                            + "from khachhang t1, kh_mua_sp t2, tongkhthanhtoan t3, tongkhcongno t4, khuvuc t5 "
                            + "where t1.makh=t2.makh and  t3.makh=t2.makh and t4.makh=t2.makh and t1.makhuvuc=t5.makhuvuc "
 		                            +"and datepart(M,t4.namtongcongno)=datepart(M,'01/14/2013 8:25:42 AM') "
		                            +"and datepart(YYYY,t4.namtongcongno)=datepart(YYYY,'01/14/2013 8:25:42 AM') "
		                            +"and datepart(M,t3.namtongthanhtoan)=datepart(M,'01/14/2013 8:25:42 AM') "
		                            +"and datepart(YYYY,t3.namtongthanhtoan)=datepart(YYYY,'01/14/2013 8:25:42 AM') "
		                            +"group by t1.stt,t1.tencuahang, t2.masp,t2.gia_sp_ngaymua,t2.gia_chietkhau_ngaymua, t3.tongthanhtoan,t4.tongcongno,t2.ghichu_khmsp "
                            +"order by t1.stt,t1.tencuahang ";
                dt = da.ExectueQueryBaoCao1(str, CommandType.Text);
            }
            else
            {
                string str = "select t1.stt, t1.tencuahang, t2.masp, sum(soluongmua)as soluong, "
                            + "(t2.gia_sp_ngaymua +t2.gia_chietkhau_ngaymua) as dongia,gia_sp_ngaymua as giagoc, sum(t2.thanhtien) as thanhtien, "
                            + "t3.tongthanhtoan as thanhtoan, t4.tongcongno as congno, t2.ghichu_khmsp as ghichu "
                            + "from khachhang t1, kh_mua_sp t2, tongkhthanhtoan t3, tongkhcongno t4, khuvuc t5 "
                            + "where t1.makh=t2.makh and  t3.makh=t2.makh and t4.makh=t2.makh and t1.makhuvuc=t5.makhuvuc "
                                    + "and t5.tenkhuvuc like N'"+tenkhuvuc+"' "
                                    + "and datepart(M,t4.namtongcongno)=datepart(M,'01/14/2013 8:25:42 AM') "
                                    + "and datepart(YYYY,t4.namtongcongno)=datepart(YYYY,'01/14/2013 8:25:42 AM') "
                                    + "and datepart(M,t3.namtongthanhtoan)=datepart(M,'01/14/2013 8:25:42 AM') "
                                    + "and datepart(YYYY,t3.namtongthanhtoan)=datepart(YYYY,'01/14/2013 8:25:42 AM') "
                            + "group by t1.stt,t1.tencuahang, t2.masp,t2.gia_sp_ngaymua,t2.gia_chietkhau_ngaymua, t3.tongthanhtoan,t4.tongcongno,t2.ghichu_khmsp "
                            + "order by t1.stt,t1.tencuahang ";
                dt = da.ExectueQueryBaoCao1(str, CommandType.Text);
            }
            return dt;
        }

        // Doanh thu Khách hàng A năm 2012 trong 1 tháng 
        public DataTable getDoanhThuKhachHang(string tenkhachhang)
        {
            DataTable dt = new DataTable();
            string str = "select row_number() over (order by tb1.ngaymua) as STT,ngaymua , masp, soluongmua,(soluongmua + gia_sp_ngaymua) as dongia,thanhtien, tb2.sotienthanhtoan, "+
                        " tb1.ghichu_khmsp " +
                        " from kh_mua_sp tb1, khachhang_thanhtoan tb2, khachhang tb3 "+
                        " where tb1.makh=tb2.makh and tb2.ghichuKHTT <> 'default'  and tb3.makh=tb2.makh and tb3.tinhtrangkh=1 "+
                        " and tb1.ngaymua = tb2.ngaythanhtoan "+
                        " and tb3.tencuahang like N'"+tenkhachhang+"' "+
                        " and datepart(M,tb1.ngaymua)=datepart(M,'01/01/2013') "+
                        " order by tb1.ngaymua";
                dt = da.ExectueQueryBaoCao1(str, CommandType.Text);
           
            return dt;
        }

        // Danh sách giao hàng khu vực
        public DataTable getDSKhachHangKV(string tenkhuvuc)
        {
            DataTable dt = new DataTable();
            DataSet ds = new DataSet();
            //Makh=0 select all           
            string str = "select row_number() over (order by t1.makh) as STT,t2.tencuahang, t3.tensp, (t4.giachietkhau + t3.giasp) as dongia, t1.tongcongno "
                            +"from  tongkhcongno t1, khachhang t2, sanpham t3, chietkhau t4 "
                            +"where t2.makh=t4.makh and t3.masp=t4.masp and t2.makh=t1.makh "
                            +"and datepart(M,t1.namtongcongno)=datepart(M,'01/14/2013 8:25:42 AM') "
                            +"and datepart(YYYY,t1.namtongcongno)=datepart(YYYY,'01/14/2013 8:25:42 AM') "
                            +"order by t2.stt ";
                dt = da.ExectueQueryBaoCao1(str, CommandType.Text);
           
            return dt;
        }

        //Danh sách báo Lỗ Lãi theo khu vực
        public DataTable getDSLoLaiKhuVuc(string tenkhuvuc)
        {
            DataTable dt = new DataTable();
            DataSet ds = new DataSet();
            //Makh=0 select all
            if (tenkhuvuc == "0")
            {
                string sql = "select t1.stt, t1.tencuahang, t2.masp, sum(soluongmua)as soluong, " +
                          " (t2.gia_sp_ngaymua +t2.gia_chietkhau_ngaymua) as dongia,gia_sp_ngaymua as giagoc, sum(t2.thanhtien) as thanhtien, " +
                          " t3.tongthanhtoan as thanhtoan, t1.stt as dautu, t1.stt as khuyenmai, t1.stt as loinhuan " +
                          " from khachhang t1, kh_mua_sp t2, tongkhthanhtoan t3, tongkhcongno t4, khuvuc t5 " +
                          " where t1.makh=t2.makh and  t3.makh=t2.makh and t4.makh=t2.makh and t1.makhuvuc=t5.makhuvuc " +
                          " and datepart(M,t4.namtongcongno)=datepart(M,'01/14/2013 8:25:42 AM') " +
                          " and datepart(YYYY,t4.namtongcongno)=datepart(YYYY,'01/14/2013 8:25:42 AM') " +
                          " and datepart(M,t3.namtongthanhtoan)=datepart(M,'01/14/2013 8:25:42 AM') " +
                          " and datepart(YYYY,t3.namtongthanhtoan)=datepart(YYYY,'01/14/2013 8:25:42 AM') " +
                          " group by t1.stt,t1.tencuahang, t2.masp,t2.gia_sp_ngaymua,t2.gia_chietkhau_ngaymua, t3.tongthanhtoan, t4.tongcongno " +
                          " order by t1.stt,t1.tencuahang ";
                dt = da.ExectueQueryBaoCao1(sql, CommandType.Text);
                //all
                //string str = "select row_number() over (order by t1.makh) as STT, t2.tencuahang,  t1.namtongcongno, tongcongno " +
                //            "from tongkhcongno t1, khachhang t2 " +
                //            "where t1.makh=t2.makh and tongcongno <> 0  and datepart(M,t1.namtongcongno)=datepart(M,'" + DateTime.Now + "')" +
                //            "order by t2.makhuvuc";
                //dt = da.ExectueQueryBaoCao1(str, CommandType.Text);
            }
            else
            {
                //khu vực
                //string str = "select row_number() over (order by t1.makh) as STT, t2.tencuahang,  t1.namtongcongno, tongcongno " +
                //            " from tongkhcongno t1, khachhang t2, khuvuc t3 " +
                //            " where t1.makh=t2.makh and tongcongno <> 0 and datepart(M,t1.namtongcongno)=datepart(M,'" + DateTime.Now + "')" +
                //            " and t2.makhuvuc=t3.makhuvuc and t3.tenkhuvuc=N'" + tenkhuvuc + "' " +
                //            " order by t2.makhuvuc";
                //dt = da.ExectueQueryBaoCao1(str, CommandType.Text);
            }
            return dt;
        }

        //Lấy công nợ của Khách hàng
        public double getCongNo(string tenquan)
        {
            double tong = 0;
            String sql = " select tongcongno from tongkhcongno " +
                        " where makh=5 and datepart(M,namtongcongno)=12 and datepart(YYYY,namtongcongno)=2012 ";
            tong = da.TraveDongDuLieu(sql);
            return tong;
        }
        public double getCongNoMoi(string tenquan)
        {
            double tong = 0;
            String sql = " select tongcongno from tongkhcongno " +
                        " where makh=5 and datepart(M,namtongcongno)=1 and datepart(YYYY,namtongcongno)=2013";
            tong = da.TraveDongDuLieu(sql);
            return tong;
        }
        
    }
}
